package edu.student.models;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.util.DB;
/**
 * 
 * @author Sai Naga Sravani Peraka
 */
public class Lookup {
	private String table="";
	private int id;
	private String name="";
	private String email_id="";
	private String password="";
	private String is_current="";
	private String interest="";
	private String faculty="";
	private String subject_name="";
	private int subject_id;
	private String subject_code;
	private String duration;
	public Lookup() {
		super();
	}
	public Lookup(String table, int id, String name, String email_id,
			String password, String is_current, String interest, String faculty, String subject_name,int subject_id, String subject_code) {
		super();
		this.table = table;
		this.id = id;
		this.name = name;
		this.email_id = email_id;
		this.password = password;
		this.is_current = is_current;
		this.interest = interest;
		this.faculty = faculty;
	}
	public String getTable() {
		return table;
	}
	public void setTable(String table) {
		this.table = table;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getEmail_id() {
		return email_id;
	}
	public void setEmail_id(String email_id) {
		this.email_id = email_id;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	public String getIs_current() {
		return is_current;
	}
	public void setIs_current(String is_current) {
		this.is_current = is_current;
	}
	public String getInterest() {
		return interest;
	}
	public void setInterest(String interest) {
		this.interest = interest;
	}	
	public String getFaculty() {
		return faculty;
	}
	public void setFaculty(String faculty) {
		this.faculty = faculty;
	}
	public String getSubject_name() {
		return subject_name;
	}
	public void setSubject_name(String subject_name) {
		this.subject_name = subject_name;
	}
	public int getSubject_id() {
		return subject_id;
	}
	public void setSubject_id(int subject_id) {
		this.subject_id = subject_id;
	}
	public String getSubject_code() {
		return subject_code;
	}
	public void setSubject_code(String subject_code) {
		this.subject_code = subject_code;
	}
	public String getDuration() {
		return duration;
	}
	public void setDuration(String duration) {
		this.duration = duration;
	}
	public static ArrayList<Lookup> find(String table,String selectionModifier) {
		ArrayList<Lookup> selection = new ArrayList<Lookup>();
		ResultSet resultSet = null;
		String query = "";
		if(table.equals("course")){
			query="select distinct(subject_id) from course "+ selectionModifier;
			System.out.println(query);
		}
		else{
			query = "select "+table+"_id,"+table+"_name, is_current "+
					"from "+table+" "+ selectionModifier;
			if(table.equals("announcement")){
				query="select announcement_id, announcement_name, is_current, interest_id, duration from announcement";
			}
			else if(table.equals("news")){
				query="select news_id, news_name, is_current, interest_id, duration from news";
			}
			else if(table.equals("subject")){
				query="select subject_id, subject_code, subject_name, is_current, faculty_id from subject";
			}
		}
		Connection connection = DB.getConnection();
		resultSet = DB.readFromDB(query, connection);
		try {
			while (resultSet.next()) {
				Lookup lookup = new Lookup();
				lookup.table=table;
				if(table.equals("course")){
					lookup.subject_id=resultSet.getInt("subject_id");
					lookup.subject_name=Subject.getSubjectName(lookup.subject_id);
				}else{
				
				lookup.id= resultSet.getInt(table+"_id");
				lookup.name = resultSet.getString(table+"_name");
				lookup.is_current = resultSet.getString("is_current");
				if(table.equals("announcement")){
					int interest_id=resultSet.getInt("interest_id");
					Lookup interestList=findOne("interest"," where interest_id="+interest_id);
					lookup.interest=interestList.getName();
					lookup.duration=resultSet.getString("duration");
				}
				if(table.equals("news")){
					int interest_id=resultSet.getInt("interest_id");
					Lookup interestList=findOne("interest"," where interest_id="+interest_id);
					lookup.interest=interestList.getName();
					lookup.duration=resultSet.getString("duration");
				}
				if(table.equals("subject")){
					int faculty_id=resultSet.getInt("faculty_id");
					Lookup facultyList=findOne("faculty"," where faculty_id="+faculty_id);
					lookup.faculty=facultyList.getName();
					lookup.subject_code=resultSet.getString("subject_code");
				}
				}
				System.out.println("lookupname:"+lookup.name);
				System.out.println("iscurrent:"+lookup.is_current);
				selection.add(lookup);
			}
		} catch (SQLException e) {
           
			e.printStackTrace();
		}
		//DB.close(resultSet);
		DB.close(connection);
		return selection;
	}
	public static Lookup findOne(String table,String selectionModifier) {
		ArrayList<Lookup> selection = new ArrayList<Lookup>();
		ResultSet resultSet = null;
		String query = "select "+table+"_id,"+table+"_name, is_current "+
				"from "+table+" "+ selectionModifier;
		if(table.equals("course")){
			query="select semester_id from course where subject_id= "+selectionModifier;
		}
		if(table.equals("announcement")){
			query="select announcement_id, announcement_name, is_current, interest_id, duration from announcement "+selectionModifier;
		}
		if(table.equals("news")){
			query="select news_id, news_name, is_current, interest_id, duration from news "+selectionModifier;
		}
		else if(table.equals("subject")){
			query="select subject_id, subject_code, subject_name, is_current, faculty_id from subject "+selectionModifier;
		}
		Connection connection = DB.getConnection();
		resultSet = DB.readFromDB(query, connection);
		Lookup lookup = new Lookup();
		try {
			if (resultSet.next()) {
				
				lookup.table=table;
				lookup.id= resultSet.getInt(table+"_id");
				lookup.name = resultSet.getString(table+"_name");
				lookup.is_current = resultSet.getString("is_current");
				if(table.equals("announcement") || table.equals("news")){
					int interest_id=resultSet.getInt("interest_id");
					Lookup interestList=findOne("interest"," where interest_id="+interest_id);
					lookup.interest=interestList.getName();
					lookup.duration=resultSet.getString("duration");
				}
				if(table.equals("subject")){
					int faculty_id=resultSet.getInt("faculty_id");
					Lookup facultyList=findOne("faculty"," where faculty_id="+faculty_id);
					lookup.faculty=facultyList.getName();
					lookup.subject_code=resultSet.getString("subject_code");
				}
				System.out.println("lookupname:"+lookup.name);
				System.out.println("iscurrent:"+lookup.is_current);
				}
		} catch (SQLException e) {
           // MyLog.myCatch("Book.java", 43, e);
			e.printStackTrace();
		}
		//DB.close(resultSet);
		DB.close(connection);
		return lookup;
	}
	public int update(String table) {
		String updateSQL = 
			    "update "+ table
				+ " set "+table+"_name = '" + name + "', is_current = '" + is_current + 
				"' where "+table+"_id = " + id;
		if(table.equals("subject")){
			Lookup facultyList=findOne("faculty"," where faculty_name='"+faculty+"'");
			int faculty_id=facultyList.getId();
			updateSQL = "update subject set subject_name = '"+name+ "', is_current = '" + is_current +"', subject_code='"+subject_code+"', faculty_id="+faculty_id +" where "+table+"_id = " + id;
		}
		if(table.equals("announcement") || table.equals("news")){
			Lookup facultyList=findOne("interest"," where interest_name='"+interest+"'");
			int interest_id=facultyList.getId();			
			updateSQL = "update "+table+" set "+table+"_name = '" + name + "', is_current = '" + is_current +"', interest_id="+interest_id+", duration='"+duration+"'" +" where "+table+"_id = " + id;
		}
		System.out.println(updateSQL);
		return DB.update(updateSQL);
	}

	public int insert(String table,String column,String selectionModifier) {
		String insertSQL = "insert into "+table
				+ " ("+table+"_name, is_current"+column+") " + "values('" + name
				+ "', '" + is_current +"'"+selectionModifier+ ");";
		System.out.println("insert query"+insertSQL);
		return DB.update(insertSQL);
	}
	public static int findOnefordelete(String table,String selectionModifier) {
		String query = "delete from "+table+" "+ selectionModifier;
		System.out.println("query"+query);
		Connection connection = DB.getConnection();
		int i= DB.deleteFromDB(query, connection);
		DB.close(connection);
		return i;
	}
	public static ArrayList<Lookup> findCourse(String selectionModifier) {
		ArrayList<Lookup> selection = new ArrayList<Lookup>();
		ResultSet resultSet = null;
		String query = "select sem.semester_id,sem.semester_name,sub.subject_id,sub.subject_name from semester sem, subject sub, course c " +
				"where c.semester_id = sem.semester_id and c.subject_id=sub.subject_id "+selectionModifier;
		Connection connection = DB.getConnection();
		resultSet = DB.readFromDB(query, connection);
		try {
			while (resultSet.next()) {
				Lookup lookup = new Lookup();
				lookup.id = resultSet.getInt("semester_id");
				lookup.name = resultSet.getString("semester_name");
				lookup.subject_id = resultSet.getInt("subject_id");
				lookup.subject_name = resultSet.getString("subject_name");
				selection.add(lookup);
			}
		} catch (SQLException e) {
           // MyLog.myCatch("Book.java", 43, e);
			e.printStackTrace();
		}
		//DB.close(resultSet);
		DB.close(connection);
		return selection;
	}
}
